package com.pentofab.payrollmanagement.database.impl;


public interface LeaveTableSQLConstants extends SQLConstants {
	
	String tableName = "leave_system";
	String EID = tableName+".EID";
	String TYPE = tableName+".TYPE";
	String From_Date= tableName+".From_Date";
	String To_Date= tableName+".To_Date";
	String No_of_Days =tableName+".No_of_Days"; 
	String Approver= tableName+".Approver"; 
	String Reason= tableName+".Reason";
	String halfDay = tableName+".halfday";
	
	String holidaysTable  = "HolidaysTable";
	String hDate = holidaysTable+".date";
	String hdescription = holidaysTable+".description";
	String nationalHoliday = holidaysTable+".NationalHoloday";
	
	String leaveBalance = new StringBuffer(select).append(openingBrace).append(convert)
			.append(openingBrace).append(MisceliniousTableSQLConstants.Value).append(comma).append(decimal).append(closingBrace)
			.append(MINUS).append(ifNull).append(openingBrace).append(openingBrace)
			.append(select).append(sum)
			.append(openingBrace).append(No_of_Days).append(closingBrace)
			.append(from).append(tableName)
			.append(where).append(EID).append(equals).append(question)
			.append(and).append(TYPE).append(equals).append(question)
			.append(closingBrace).append(comma).append("0").append(closingBrace)
			.append(closingBrace).append(from).append(MisceliniousTableSQLConstants.tableName).append(where)
			.append(MisceliniousTableSQLConstants.configuration).append(equals).append(question).toString();
	String applyLeave = new StringBuffer(insert_into).append(tableName)
			.append(openingBrace).append(EID).append(comma)
			.append(TYPE).append(comma)
			.append(From_Date).append(comma)
			.append(To_Date).append(comma)
			.append(No_of_Days).append(comma)
			.append(Approver).append(comma)
			.append(Reason).append(comma)
			.append(halfDay).append(closingBrace).append(values)
			.append(openingBrace)
			.append(question).append(comma)
			.append(question).append(comma)
			.append(question).append(comma)
			.append(question).append(comma)
			.append(question).append(comma)
			.append(question).append(comma)
			.append(question).append(comma)
			.append(question).append(closingBrace)
			.toString();
	String leaveHistory = new StringBuffer(select).append(LeaveTableSQLConstants.TYPE).append(comma)
			.append(LeaveTableSQLConstants.From_Date)
			.append(comma).append(LeaveTableSQLConstants.To_Date).append(comma)
			.append(LeaveTableSQLConstants.No_of_Days).append(comma)
			.append(LeaveTableSQLConstants.Reason).append(from)
			.append(LeaveTableSQLConstants.tableName).append(where)
			.append(LeaveTableSQLConstants.EID).append(equals).append(question)
			.append(and).append(str2Date).append(openingBrace)
			.append(LeaveTableSQLConstants.From_Date).append(comma).append(sqldateformat).append(closingBrace)
			.append(greaterthanequals).append(str2Date).append(openingBrace).append(question).append(comma).append(sqldateformat).append(closingBrace)
			.append(ORDEY_BY).append(From_Date).toString();
	
	
	String cancelLeave = new StringBuffer(delete).append(from).append(tableName)
			   .append(where).append(EID).append(equals).append(question).append(and)
			   .append(From_Date).append(equals).append(question).append(and)
			   .append(To_Date).append(equals).append(question).toString();

	String empOnLeave = new StringBuffer(select).append(EID).append(from).append(tableName)
			   .append(where).append(EID).append(equals).append(question).append(and)
			   .append(str2Date).append(openingBrace).append(From_Date).append(comma).append(sqldateformat).append(closingBrace)
			   .append(lessthanequals).append(str2Date).append(openingBrace).append(question).append(comma).append(sqldateformat).append(closingBrace).append(and)
			    .append(str2Date).append(openingBrace).append(To_Date).append(comma).append(sqldateformat).append(closingBrace)
			  .append(greaterthanequals).append(str2Date).append(openingBrace).append(question).append(comma).append(sqldateformat).append(closingBrace)
			  
			  .append(and).append(1).append(equals).append(openingBrace).append(CASE).append(WHEN).append(halfDay).append(equals).append("'11'")
			  .append(and).append(openingBrace).append(From_Date).append(equals).append(question).append(OR).append(To_Date).append(equals).append(question)
			  .append(closingBrace)
			  .append(then).append(0).append(ELSE).append(1).append(END).append(closingBrace)
			  
			   .append(and).append(1).append(equals).append(openingBrace).append(CASE).append(WHEN).append(halfDay).append(equals).append("'10'")
			  .append(and).append(openingBrace).append(From_Date).append(equals).append(question)
			  .append(closingBrace)
			  .append(then).append(0).append(ELSE).append(1).append(END).append(closingBrace)
			  
			  .append(and).append(1).append(equals).append(openingBrace).append(CASE).append(WHEN).append(halfDay).append(equals).append("'01'")
			  .append(and).append(openingBrace).append(To_Date).append(equals).append(question)
			  .append(closingBrace)
			  .append(then).append(0).append(ELSE).append(1).append(END).append(closingBrace)
			  .toString();

	String getHolidays = new StringBuffer(select).append(hDate).append(comma).append(hdescription).append(comma).append(nationalHoliday)
			.append(from).append(holidaysTable).append(ORDEY_BY).append(str2Date).append(openingBrace)
			.append(LeaveTableSQLConstants.hDate).append(comma).append(sqldateformat).append(closingBrace)
			.toString();
	
	String addHoliday = new StringBuffer(insert_into).append(holidaysTable).append(openingBrace)
			.append(hDate).append(comma).append(hdescription).append(comma).append(nationalHoliday).append(closingBrace).append(values)
			.append(openingBrace).append("STR_TO_DATE(?,'%d-%m-%Y'),?,?").append(closingBrace).toString();

	String deleteHoliday = new StringBuffer(delete).append(from).append(holidaysTable)
			.append(where).append(hDate).append(equals).append(question).toString();
	
	String getFromLaveMster = "Select EL,CL,SL from Leave_Master";
	String updateLeaveMaster = "update leave_master set EL = ?,CL =?, SL = ?";
}
